/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import NM_ClasesGenerales.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author USER
 */
public class ClienteDO {

    PreparedStatement oPreparedStatement = null;
    ResultSet oResultSet = null;
    Connection oConnection = null;

    private Connection getConnection() throws SQLException {
        Connection conn;
        //BDConexFactory fact = new BDConexFactory(dataSource);
        conn = BDConexFactory.getInstance().getConnection();
        return conn;
    }

    public List LeerArchivoUsuario(String email, String pwd) throws SQLException {

        ArrayList listaFinal = new ArrayList();
        Cliente emp = new Cliente();
        String variableBD = "VIAJES";
        try {

            oConnection = getConnection();

            String sql = "SELECT co_user,no_user,co_passwd,no_email, "
                    + " co_estado,"
                    + variableBD + ".USUARIO.co_empresa, "
                    + " co_ruc,no_empresa,no_direccion,No_fono "
                    + " FROM "
                    + variableBD + ".USUARIO "
                    + " INNER JOIN "
                    + variableBD + ".empresa ON "
                    + variableBD + ".USUARIO.CO_EMPRESA = "
                    + variableBD + ".empresa.co_empresa "
                    + " WHERE NO_EMAIL = '" + email + "' AND CO_PASSWD = '" + pwd + "'";

            oPreparedStatement = oConnection.prepareStatement(sql);

            oResultSet = oPreparedStatement.executeQuery();

            while (oResultSet.next()) {
                emp = new Cliente();
                emp.setoIdUser(Integer.parseInt(oResultSet.getString("CO_USER")));
                emp.setoNombre((oResultSet.getString("NO_USER")));
                emp.setClave((oResultSet.getString("CO_PASSWD")));
                emp.setoEmail((oResultSet.getString("NO_EMAIL")));
                emp.setoIdCliente((oResultSet.getString("CO_EMPRESA")));
                emp.setState(Byte.parseByte(oResultSet.getString("CO_ESTADO")));
                emp.setRuc((oResultSet.getString("co_ruc")));
                emp.setCliente((oResultSet.getString("no_empresa")));
                emp.setDireccion((oResultSet.getString("no_direccion")));
                emp.setTelefono((oResultSet.getInt("No_fono")));
                listaFinal.add(emp);
            }

        } catch (Exception e) {
        } finally {

            try {
                if (oResultSet != null) {
                    oResultSet.close();
                }
            } catch (SQLException e) {
                System.out.println("DAO buscarUsuarioBD()1"
                        + e.getMessage());


            } finally {

                try {
                    if (oPreparedStatement != null) {
                        oPreparedStatement.close();
                    }
                } catch (SQLException e) {
                    System.out.println("DAO buscarUsuarioBD()2"
                            + e.getMessage());

                }
                try {
                    if (oResultSet != null) {
                        oResultSet.close();
                        System.out.println("DAO buscarUsuarioBD() cerrando conexion a  Base de Datos");
                    }
                } catch (SQLException e) {
                    System.out.println("DAO buscarUsuarioBD()3"
                            + e.getMessage());
                }
            }
            return listaFinal;

        }
    }

    public void InsertClassIti(List<Itinerario> lstpass) throws SQLException {

        int intRes = 0;
        String variableBD = "VIAJES";
        try {

            oConnection = getConnection();
            String sql = "insert into "
                    + variableBD
                    + ".ITINERARIO("
                    + " numItinerario ,strAir , numFly ,strClase ,"
                    + "strStatus ,strCityDeparture ,strDateTimeDeparture ,StrCityArrival ,StrDateTimeArryval,bolMarca "
                    + "  ) values(?,?,?,?,?,?,?,?,?)";

            oPreparedStatement = oConnection.prepareStatement(sql);
            for (int i = 0; i < lstpass.size(); i++) {
                oPreparedStatement.setInt(1, lstpass.get(i).getNumItinerario());
                oPreparedStatement.setString(2, lstpass.get(i).getStrAir());
                oPreparedStatement.setString(3, lstpass.get(i).getNumFly());
                oPreparedStatement.setString(4, lstpass.get(i).getStrClase());
                oPreparedStatement.setString(5, lstpass.get(i).getStrStatus());
                oPreparedStatement.setString(6, lstpass.get(i).getStrCityDeparture());
                oPreparedStatement.setString(7, lstpass.get(i).getStrDateTimeDeparture());
                oPreparedStatement.setString(8, lstpass.get(i).getStrDateTimeArryval());
                oPreparedStatement.setInt(9, lstpass.get(i).getBolMarca());

                intRes = oPreparedStatement.executeUpdate();
            }

        } catch (Exception e) {
        } finally {

            try {
                if (oResultSet != null) {
                    oResultSet.close();
                }
            } catch (SQLException e) {
                System.out.println("DAO buscarUsuarioBD()1"
                        + e.getMessage());


            } finally {

                try {
                    if (oPreparedStatement != null) {
                        oPreparedStatement.close();
                    }
                } catch (SQLException e) {
                    System.out.println("DAO buscarUsuarioBD()2"
                            + e.getMessage());

                }
                try {
                    if (oResultSet != null) {
                        oResultSet.close();
                        System.out.println("DAO buscarUsuarioBD() cerrando conexion a  Base de Datos");
                    }
                } catch (SQLException e) {
                    System.out.println("DAO buscarUsuarioBD()3"
                            + e.getMessage());
                }
            }
        }
    }

    public void InsertClassPass(List<Passenger> lstpass) throws SQLException {

        int intRes = 0;
        String variableBD = "VIAJES";
        try {

            oConnection = getConnection();
            String sql = "insert into "
                    + variableBD
                    + ".PASAJERO("
                    + " NumPassenger ,StrPassengerName , StrPassengerSurname ,strDateBirthday ,"
                    + "strGenero ,strPassenegerType ,strReferenceType ,strReferenceNumber ,bolMarca "
                    + "  ) values(?,?,?,?,?,?,?,?,?)";

            oPreparedStatement = oConnection.prepareStatement(sql);
            for (int i = 0; i < lstpass.size(); i++) {
                oPreparedStatement.setInt(1, lstpass.get(i).getNumPassenger());
                oPreparedStatement.setString(2, lstpass.get(i).getStrPassengerName());
                oPreparedStatement.setString(3, lstpass.get(i).getStrPassengerSurname());
                oPreparedStatement.setString(4, lstpass.get(i).getStrDateBirthday());
                oPreparedStatement.setString(5, lstpass.get(i).getStrGenero());
                oPreparedStatement.setString(6, lstpass.get(i).getStrPassenegerType());
                oPreparedStatement.setString(7, lstpass.get(i).getStrReferenceNumber());
                oPreparedStatement.setInt(8, lstpass.get(i).getBolMarca());
                intRes = oPreparedStatement.executeUpdate();
            }

        } catch (Exception e) {
        } finally {

            try {
                if (oResultSet != null) {
                    oResultSet.close();
                }
            } catch (SQLException e) {
                System.out.println("DAO buscarUsuarioBD()1"
                        + e.getMessage());


            } finally {

                try {
                    if (oPreparedStatement != null) {
                        oPreparedStatement.close();
                    }
                } catch (SQLException e) {
                    System.out.println("DAO buscarUsuarioBD()2"
                            + e.getMessage());

                }
                try {
                    if (oResultSet != null) {
                        oResultSet.close();
                        System.out.println("DAO buscarUsuarioBD() cerrando conexion a  Base de Datos");
                    }
                } catch (SQLException e) {
                    System.out.println("DAO buscarUsuarioBD()3"
                            + e.getMessage());
                }
            }
//            return listaFinal;

        }
    }

    public Usuario BucarUsuario(String oEmailUsuario) throws SQLException {

        Usuario oUsuario = null;

        try {

            oConnection = getConnection();
            String sql = "SELECT APP.USUARIO.CO_CLIENTE, APP.USUARIO.NO_NOMBREUSUARIO FROM APP.USUARIO WHERE APP.USUARIO.NO_EMAILUSUARIO = ?";

            oPreparedStatement = oConnection.prepareStatement(sql);
            oPreparedStatement.setString(1, oEmailUsuario);
            oResultSet = oPreparedStatement.executeQuery();

            oUsuario = new Usuario();

            while (oResultSet.next()) {

                oUsuario.setoNombre(oResultSet.getString("NO_NOMBREUSUARIO"));
                oUsuario.setoIdCliente(oResultSet.getString("CO_CLIENTE"));



//                BeanArancel ara = new BeanArancel();
//                ara.setCodigoArancel(resulSet.getString("PRODUCT_CODE"));
//                ara.setDescripcion(resulSet.getString("DESCRIPTION"));
//                ara.setPrecioUnitario(resulSet.getBigDecimal("PURCHASE_COST"));
//                listaAranceles.add(ara);
            }

        } catch (Exception e) {
        } finally {

            try {
                if (oResultSet != null) {
                    oResultSet.close();
                }
            } catch (SQLException e) {
                System.out.println("DAOArancel buscarArancelBD()"
                        + e.getMessage());


            } finally {

                try {
                    if (oPreparedStatement != null) {
                        oPreparedStatement.close();
                    }
                } catch (SQLException e) {
                    System.out.println("DAOArancel buscarArancelBD()"
                            + e.getMessage());


                }
                try {
                    if (oConnection != null) {
                        oConnection.close();
                        System.out.println("DAOArancel buscarArancelBD() cerrando conexion a  Base de Datos");

                    }
                } catch (SQLException e) {
                    System.out.println("DAOArancel buscarArancelBD()"
                            + e.getMessage());


                }

            }

            return oUsuario;

        }



    }
}
